
from openpyxl import load_workbook

class ExcelUtil:
    def __init__(self,file_name,sheet_name):
        """Excel封装"""
        self.file_name = file_name
        self.sheet_name = sheet_name


    def open(self):
        self.wb = load_workbook(self.file_name)
        if isinstance(self.sheet_name, int):
            self.sheet = self.wb.worksheets[self.sheet_name]
        else:
            self.sheet = self.wb[self.sheet_name]



    # def choose_sheet(self,sheet_name):
    #     """选择表单
    #     sheet_name是整数，根据索引获取
    #     如果是字符串，根据名字获取
    #
    #     """
    #
    #     if isinstance(sheet_name,int):
    #         return self.wb.worksheets[sheet_name]
    #     return self.wb[sheet_name]


    def title(self):
        '''获取标题行数据添加到列表'''
        # sheet = self.sheet(sheet_name)
        # # 获取第一行数据,就是标题
        # title = []
        # for col in sheet[1]:
        #     print(col.value)
        #     title.append(col.value)
        # return title
        self.open()
        title =  [c.value for c in self.sheet[1]]
        self.wb.close()
        return title




    def read(self, start_row=2,start_column=1):
        """获取所有行数据"""
        self.open()
        sheet = self.sheet
        #获取第一行数据,就是标题
        title = [c.value for c in self.sheet[1]]
        self.wb.close()

        # title = []
        # for col in sheet[1]:
        #     print(col.value)
        #     title.append(col.value)

        data=[]
        for row in range(start_row,sheet.max_row + 1):
            row_data=[]
            for column in range(start_column,sheet.max_column + 1):
                row_data.append(sheet.cell(row,column).value)
            #将表头和每列变成字典
            row_data = dict(zip(title,row_data))
            data.append(row_data)
        return data


    def save(self):
        """保存"""
        self.wb.save(self.file_name)
        self.wb.close()



    def write(self,row,column,data):
        self.open()
        # wb = load_workbook(self.file_name)
        self.sheet.cell(row,column).value = data

        #保存关闭
        self.save()


if __name__ == '__main__':
    file_path = "E:\workspace\python\study_code\data\data.xlsx"

    xls = ExcelUtil(file_path,'login')
    test_title = xls.title()
    print(xls.title())
    result_index = test_title.index('result')
    print(result_index)

    # print(test_title)
    # print(result_index = test_title.index('result'))


